# encoding:utf-8
import dmPython
import sqlalchemy
import threading
import time
import pyodbc as ODBC
import sys

print("test")
select={'84':0,'85':0}
insert={'84':0,'85':0}

from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker

engine = create_engine('dm://SYSDBA:SYSDBA@DMRW', connect_args={'local_code': 1, 'connection_timeout': 15,'rwseparate':1,'rwseparate_percent':10},
                       pool_size=200,pool_pre_ping=True)
                       #pool=sqlalchemy.pool.QueuePool)
DB_Session = sessionmaker(bind=engine)
session = DB_Session()
print(session.execute('select count(*) from v$version;').first())
#print(session.execute('create table test_1(v1 int,v2 int);'))
print(session.execute('select instance_name from v$instance;').first())
#print(session.execute('select * from v$version;').items())

result = session.execute("select * from v$version;")
for row in result:
    print("banner:", row['banner'].strip())

'''
try:
    con_base=ODBC.connect("DRIVER={DM7 ODBC DRIVER};SERVER=DMRW;UID=SYSDBA;PWD=SYSDBA;rwseparate=1;rwseparate_percent=80;CHARACTER_CODE=PG_UTF8;")
except Exception as err:
    print(err)
    sys.exit(0)
'''

def fun_sel():
    time.sleep(0.1)
    session=DB_Session()
    #session = con_base.cursor()
    insname=session.execute('select instance_name from v$instance;').first()[0]
    #insname = session.execute('select instance_name from v$instance;').fetchone()[0]
    if(insname=='TEST_DM_NAME84'):
        select['84']+=1
    else:
        select['85']+=1
    session.close()
    #print('-select-------------------------------->'+insname+'-->')

def fun_ins():
    time.sleep(0.1)
    session=DB_Session()
    #session = con_base.cursor()
    insname = session.execute('insert into test_1 values(1,3)')
    insname = session.execute('select instance_name from v$instance;').first()[0]
    #insname = session.execute('select instance_name from v$instance;').fetchone()[0]
    if(insname=='TEST_DM_NAME84'):
        insert['84']+=1
    else:
        insert['85']+=1
    session.commit();
    session.close()
    #print('-insert->'+insname+'-->'+session.execute('select count(*) from test_1;').first()[0])
'''
Num_all = 10;
for pj in range(Num_all):
    # p=multiprocessing.Process(target=fun_select,args=(sql,pj,s,s_time)) # 单个参数是要用 (sql,) 否则 sql 会被逐个字母解析
    p = threading.Thread(target=fun_sel, args=(pj,))
    p.start()
    p = threading.Thread(target=fun_ins, args=(pj,))
    p.start()
p.join()
'''

while True:
    try:
        for rs  in range(10):

                p = threading.Thread(target=fun_sel, args=())
                j = threading.Thread(target=fun_ins, args=())
        p.start()
        j.start()
        p.join()
        j.join()
    except Exception as err:
        print(err)
        time.sleep(2)
    time.sleep(0.3)
    print('select',select)
    print('insert',insert)

